--    Program: 		CAPARIMP_TRANSATCTION_SOURCE.sql
--    Description:  Import AR Transaction Source information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_batch_source_id		number;
                    		                 		
   cursor cur_tran_source is
      select * from CRST_RA_BATCH_SOURCES_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_tran_source loop  
   	 l_err_msg_tmp := '';
   	  
   	 select RA_BATCH_SOURCES_S.NEXTVAL
   	 into   l_batch_source_id
   	 from   dual;
   	 
   	 update CRST_RA_BATCH_SOURCES_ALL
   	 set    batch_source_id_n = l_batch_source_id,
   	        org_id_n = CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID)
   	 where  batch_source_id = cur.BATCH_SOURCE_ID
   	 and    org_id = cur.ORG_ID;
   	 
   	 begin	     
       -- import data
       insert into RA_BATCH_SOURCES_ALL
       (
         BATCH_SOURCE_ID              ,
         LAST_UPDATE_DATE             ,
         LAST_UPDATED_BY              ,
         CREATION_DATE                ,
         CREATED_BY                   ,
         LAST_UPDATE_LOGIN            ,
         NAME                         ,
         ORG_ID                       ,
         DESCRIPTION                  ,
         STATUS                       ,
         LAST_BATCH_NUM               ,
         DEFAULT_INV_TRX_TYPE         ,
         ATTRIBUTE_CATEGORY           ,
         ATTRIBUTE1                   ,
         ATTRIBUTE2                   ,
         ATTRIBUTE3                   ,
         ATTRIBUTE4                   ,
         ATTRIBUTE5                   ,
         ATTRIBUTE6                   ,
         ATTRIBUTE7                   ,
         ATTRIBUTE8                   ,
         ATTRIBUTE9                   ,
         ATTRIBUTE10                  ,
         ACCOUNTING_FLEXFIELD_RULE    ,
         ACCOUNTING_RULE_RULE         ,
         AGREEMENT_RULE               ,
         AUTO_BATCH_NUMBERING_FLAG    ,
         AUTO_TRX_NUMBERING_FLAG      ,
         BATCH_SOURCE_TYPE            ,
         BILL_ADDRESS_RULE            ,
         BILL_CONTACT_RULE            ,
         BILL_CUSTOMER_RULE           ,
         CREATE_CLEARING_FLAG         ,
         CUST_TRX_TYPE_RULE           ,
         DERIVE_DATE_FLAG             ,
         END_DATE                     ,
         FOB_POINT_RULE               ,
         GL_DATE_PERIOD_RULE          ,
         INVALID_LINES_RULE           ,
         INVALID_TAX_RATE_RULE        ,
         INVENTORY_ITEM_RULE          ,
         INVOICING_RULE_RULE          ,
         MEMO_REASON_RULE             ,
         REV_ACC_ALLOCATION_RULE      ,
         SALESPERSON_RULE             ,
         SALES_CREDIT_RULE            ,
         SALES_CREDIT_TYPE_RULE       ,
         SALES_TERRITORY_RULE         ,
         SHIP_ADDRESS_RULE            ,
         SHIP_CONTACT_RULE            ,
         SHIP_CUSTOMER_RULE           ,
         SHIP_VIA_RULE                ,
         SOLD_CUSTOMER_RULE           ,
         START_DATE                   ,
         TERM_RULE                    ,
         UNIT_OF_MEASURE_RULE         ,
         ATTRIBUTE11                  ,
         ATTRIBUTE12                  ,
         ATTRIBUTE13                  ,
         ATTRIBUTE14                  ,
         ATTRIBUTE15                  ,
         CUSTOMER_BANK_ACCOUNT_RULE   ,
         MEMO_LINE_RULE               ,
         RECEIPT_METHOD_RULE          ,
         RELATED_DOCUMENT_RULE        ,
         ALLOW_SALES_CREDIT_FLAG      ,
         GROUPING_RULE_ID             ,
         CREDIT_MEMO_BATCH_SOURCE_ID  ,
         GLOBAL_ATTRIBUTE1            ,
         GLOBAL_ATTRIBUTE2            ,
         GLOBAL_ATTRIBUTE3            ,
         GLOBAL_ATTRIBUTE4            ,
         GLOBAL_ATTRIBUTE5            ,
         GLOBAL_ATTRIBUTE6            ,
         GLOBAL_ATTRIBUTE7            ,
         GLOBAL_ATTRIBUTE8            ,
         GLOBAL_ATTRIBUTE9            ,
         GLOBAL_ATTRIBUTE10           ,
         GLOBAL_ATTRIBUTE11           ,
         GLOBAL_ATTRIBUTE12           ,
         GLOBAL_ATTRIBUTE13           ,
         GLOBAL_ATTRIBUTE14           ,
         GLOBAL_ATTRIBUTE15           ,
         GLOBAL_ATTRIBUTE16           ,
         GLOBAL_ATTRIBUTE17           ,
         GLOBAL_ATTRIBUTE18           ,
         GLOBAL_ATTRIBUTE19           ,
         GLOBAL_ATTRIBUTE20           ,
         GLOBAL_ATTRIBUTE_CATEGORY    ,
         COPY_DOC_NUMBER_FLAG         ,
         DEFAULT_REFERENCE
       )
       values
       (
         l_batch_source_id       		  ,
         cur.LAST_UPDATE_DATE             ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.CREATION_DATE                ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.LAST_UPDATE_LOGIN            ,
         cur.NAME                         ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.DESCRIPTION                  ,
         cur.STATUS                       ,
         cur.LAST_BATCH_NUM               ,
         CAPARIMP_MAPPING.GetTransactionTypeIDMapping(cur.ORG_ID, cur.DEFAULT_INV_TRX_TYPE), 
         cur.ATTRIBUTE_CATEGORY           ,
         cur.ATTRIBUTE1                   ,
         cur.ATTRIBUTE2                   ,
         cur.ATTRIBUTE3                   ,
         cur.ATTRIBUTE4                   ,
         cur.ATTRIBUTE5                   ,
         cur.ATTRIBUTE6                   ,
         cur.ATTRIBUTE7                   ,
         cur.ATTRIBUTE8                   ,
         cur.ATTRIBUTE9                   ,
         cur.ATTRIBUTE10                  ,
         cur.ACCOUNTING_FLEXFIELD_RULE    ,
         cur.ACCOUNTING_RULE_RULE         ,
         cur.AGREEMENT_RULE               ,
         cur.AUTO_BATCH_NUMBERING_FLAG    ,
         cur.AUTO_TRX_NUMBERING_FLAG      ,
         cur.BATCH_SOURCE_TYPE            ,
         cur.BILL_ADDRESS_RULE            ,
         cur.BILL_CONTACT_RULE            ,
         cur.BILL_CUSTOMER_RULE           ,
         cur.CREATE_CLEARING_FLAG         ,
         cur.CUST_TRX_TYPE_RULE           ,
         cur.DERIVE_DATE_FLAG             ,
         cur.END_DATE                     ,
         cur.FOB_POINT_RULE               ,
         cur.GL_DATE_PERIOD_RULE          ,
         cur.INVALID_LINES_RULE           ,
         cur.INVALID_TAX_RATE_RULE        ,
         cur.INVENTORY_ITEM_RULE          ,
         cur.INVOICING_RULE_RULE          ,
         cur.MEMO_REASON_RULE             ,
         cur.REV_ACC_ALLOCATION_RULE      ,
         cur.SALESPERSON_RULE             ,
         cur.SALES_CREDIT_RULE            ,
         cur.SALES_CREDIT_TYPE_RULE       ,
         cur.SALES_TERRITORY_RULE         ,
         cur.SHIP_ADDRESS_RULE            ,
         cur.SHIP_CONTACT_RULE            ,
         cur.SHIP_CUSTOMER_RULE           ,
         cur.SHIP_VIA_RULE                ,
         cur.SOLD_CUSTOMER_RULE           ,
         cur.START_DATE                   ,
         cur.TERM_RULE                    ,
         cur.UNIT_OF_MEASURE_RULE         ,
         cur.ATTRIBUTE11                  ,
         cur.ATTRIBUTE12                  ,
         cur.ATTRIBUTE13                  ,
         cur.ATTRIBUTE14                  ,
         cur.ATTRIBUTE15                  ,
         cur.CUSTOMER_BANK_ACCOUNT_RULE   ,
         cur.MEMO_LINE_RULE               ,
         cur.RECEIPT_METHOD_RULE          ,
         cur.RELATED_DOCUMENT_RULE        ,
         cur.ALLOW_SALES_CREDIT_FLAG      ,
         cur.GROUPING_RULE_ID             ,
         cur.CREDIT_MEMO_BATCH_SOURCE_ID  , --NULL
         cur.GLOBAL_ATTRIBUTE1            ,
         cur.GLOBAL_ATTRIBUTE2            ,
         cur.GLOBAL_ATTRIBUTE3            ,
         cur.GLOBAL_ATTRIBUTE4            ,
         cur.GLOBAL_ATTRIBUTE5            ,
         cur.GLOBAL_ATTRIBUTE6            ,
         cur.GLOBAL_ATTRIBUTE7            ,
         cur.GLOBAL_ATTRIBUTE8            ,
         cur.GLOBAL_ATTRIBUTE9            ,
         cur.GLOBAL_ATTRIBUTE10           ,
         cur.GLOBAL_ATTRIBUTE11           ,
         cur.GLOBAL_ATTRIBUTE12           ,
         cur.GLOBAL_ATTRIBUTE13           ,
         cur.GLOBAL_ATTRIBUTE14           ,
         cur.GLOBAL_ATTRIBUTE15           ,
         cur.GLOBAL_ATTRIBUTE16           ,
         cur.GLOBAL_ATTRIBUTE17           ,
         cur.GLOBAL_ATTRIBUTE18           ,
         cur.GLOBAL_ATTRIBUTE19           ,
         cur.GLOBAL_ATTRIBUTE20           ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY    ,
         cur.COPY_DOC_NUMBER_FLAG         ,
         cur.DEFAULT_REFERENCE
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  
   end loop;
   
   if l_rejected_count <> 0 then   
     commit;
   else
   	 rollback;  
   end if;

   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_TRANSACTION_SOURCE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit